USE Vehicle
GO

/* Clear the old tables */
IF OBJECT_ID('vds_manager_user_permission') IS NOT NULL
	DROP TABLE vds_manager_user_permission;
GO

IF OBJECT_ID('vds_manager_role') IS NOT NULL
	DROP TABLE vds_manager_role;
GO

IF OBJECT_ID('vds_manager_user') IS NOT NULL
	DROP TABLE vds_manager_user;
GO

/* Create tables */
CREATE TABLE vds_manager_user
(
	_id INT IDENTITY PRIMARY KEY,
	_user VARCHAR(64) NOT NULL UNIQUE,
	_password CHAR(32),
	_isEnabled BIT NOT NULL,
	_createdBy INT,
	_createdTime DATETIME DEFAULT GETDATE() NOT NULL,
	FOREIGN KEY (_createdBy) REFERENCES vds_manager_user(_id),
);
GO

CREATE TABLE vds_manager_role
(
	_id INT PRIMARY KEY,
	_roleName VARCHAR(64) NOT NULL UNIQUE,
	_createdBy INT,
	_createdTime DATETIME DEFAULT GETDATE() NOT NULL,
	FOREIGN KEY (_createdBy) REFERENCES vds_manager_user(_id),
);
GO

CREATE TABLE vds_manager_user_permission
(
	_id INT IDENTITY PRIMARY KEY,
	_userId INT NOT NULL,
	_roleId INT NOT NULL,
	_createdBy INT,
	_createdTime DATETIME DEFAULT GETDATE() NOT NULL,
	_note VARCHAR(1024),
	FOREIGN KEY (_userId) REFERENCES vds_manager_user(_id),
	FOREIGN KEY (_roleId) REFERENCES vds_manager_role(_id),
	FOREIGN KEY (_createdBy) REFERENCES vds_manager_user(_id),
);
GO

/* Create triggers */
CREATE TRIGGER vds_manager_user_delete_trigger
ON vds_manager_user
INSTEAD OF DELETE
AS
BEGIN
	DELETE FROM vds_manager_user_permission WHERE _userId IN (SELECT _id FROM deleted);
	UPDATE vds_manager_user SET _createdBy = NULL WHERE _createdBy IN (SELECT _id FROM deleted);
	UPDATE vds_manager_role SET _createdBy = NULL WHERE _createdBy IN (SELECT _id FROM deleted);
	DELETE FROM vds_manager_user WHERE _id IN (SELECT _id FROM deleted);
END;
GO

CREATE TRIGGER vds_manager_role_delete_trigger
ON vds_manager_role
INSTEAD OF DELETE
AS
BEGIN
	DELETE FROM vds_manager_user_permission WHERE _roleId IN (SELECT _id FROM deleted);
	DELETE FROM vds_manager_role WHERE _id IN (SELECT _id FROM deleted);
END;
GO

/* Initialize data */

/* Administrator of VDS Manager */
/* User: Administrator Password: Administrator */
INSERT INTO vds_manager_user (_user, _password, _isEnabled) VALUES ('Administrator', '32135d93ba40af83aecf6fb591b42116', 1);
GO

/* Create the basic roles for VDS Manager */
DECLARE @createdBy INT;
SET @createdBy = (SELECT _id FROM vds_manager_user WHERE _user = 'Administrator');
INSERT INTO vds_manager_role (_id, _roleName, _createdBy) VALUES (1, 'ROLE_ADMINISTRATOR', @createdBy);
INSERT INTO vds_manager_role (_id, _roleName, _createdBy) VALUES (2, 'ROLE_USER', @createdBy);
GO

/* Grant roles to user Administrator */
DECLARE @userId INT;
SET @userId = (SELECT _id FROM vds_manager_user WHERE _user = 'Administrator');
DECLARE @roleId INT;
SET @roleId = (SELECT _id FROM vds_manager_role WHERE _roleName = 'ROLE_ADMINISTRATOR');
INSERT INTO vds_manager_user_permission (_userId, _roleId) VALUES (@userId, @roleId);
SET @roleId = (SELECT _id FROM vds_manager_role WHERE _roleName = 'ROLE_USER');
INSERT INTO vds_manager_user_permission (_userId, _roleId) VALUES (@userId, @roleId);
GO